from three.excel import sheet
from  three.mysql1 import db
from three.sqlmany import db1
import xlrd
sql="create table`person`(`username` varchar(20),`age` int,`sex` char(4),`high` int)"
sql1="CREATE TABLE`user_info` (`id` int(11) ,`idcard` varchar(50) ,`username` varchar(50) ,`realname` varchar(50) ,`pwd` varchar(50) ,`telphone` varchar(12) ,`email` varchar(100) , `age` int(11) ,`sex` varchar(20) ,`address` varchar(200) ,`hiredate` date ,`sal` double(9,2) ,`job` varchar(100) ,`company` varchar(100) );"

#操作1，对person表加入十条数据

sql2 = "INSERT INTO`person` VALUES (%s, %s, %s, %s)"
values = [
    ("张三", 12, "男", 175),
    ("李四", 15, "女", 160),
    ("王五", 22, "男", 180),
    ("李二",20,'男',173),
]

open1=xlrd.open_workbook(filename='baidu-员工的人员信息.xls')
sheet1=open1.sheet_by_index(0)

sql3="INSERT INTO`user_info` VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

value1=[]
for v in range(65000,65534):
    value1.append(sheet1.row_values(v))

values3 = []
for value in value1:
    value3 = []
    for item in value:
        if isinstance(item, float) and item.is_integer():
            value3.append(int(item))
        else:
            value3.append(item)
    values3.append(tuple(value3))

print(values3)
#db(sql)
#db(sql1)
#db(sql2)
#db(sql3)
#db1(sql2,values)
#db1(sql3, values3)


